﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Text.RegularExpressions;
using System.Data;
using System.IO;
using System.Collections;
using System.Web;
using ExcelLibrary;

/* Class: CSVFile
 *          Used for importing and exporting simple CSV Files
 * by: Phil Allison
 * dt: 6/4/09
 * 
 */
namespace h_base
{
    [Serializable]
    public class CSVFile
    {
		private string m_sep = ",";
		private string i_sep = ",";
		private int m_cols;
		private bool m_HasHeaderRow = true;
		private int m_rows;
		private ArrayList m_err = new ArrayList();
		private bool _alwaysQuote = false;
		private bool _crlf = false;

		#region Properties

		public ArrayList ErrorList
		{
			get { return m_err; }
			}
		public string Separator
		{
			get { return m_sep; }

			set { m_sep = value; }
			}
		public int Columns
		{
			get { return m_cols; }
			}
		public int RowsLoaded
		{
			get { return m_rows; }
			}
		public bool HasHeaderRow
		{
			get { return m_HasHeaderRow; }
			set { m_HasHeaderRow = value; }
		}

		// always quote text based fields
		public bool AlwaysQuote
			{
			get { return _alwaysQuote; }
			set { _alwaysQuote = value; }
			}

		public bool UseCRLF
			{
			get { return _crlf; }
			set { _crlf = value; }
			}
		#endregion

		public CSVFile()
		{
			m_cols = 0;
			m_rows = 0;
			m_HasHeaderRow = false;
		}

		/// <summary>
		/// Attempt to clean CSV file column data before returning it. 
		/// </summary>
		/// <param name="dr">DataRow item containing column</param>
		/// <param name="index">Index of column to be cleaned and returned</param>
		/// <param name="altIndex">Alternate Index if first one is empty</param>
		/// <param name="condition">flag indicating whether first index should be used (true) or alternate index (false)</param>
		/// <returns></returns>
		public static string Cleanup(DataRow dr, int index, int altIndex, bool condition)
		{
			string coldata = "";
			// logic:
			// if index within array, and the condition is True, then use that column data.
			// otherwise, if altIndex is within array, use the alternate index item. 
			if (dr.ItemArray.Length > index && condition)
				coldata = dr[index].ToString();
			else if (altIndex > -1 && dr.ItemArray.Length > altIndex)
				coldata = dr[altIndex].ToString();
			//trim and remove commas from item
			return Cleanup(coldata);
		}
		public static string Cleanup(DataRow dr, int index)
		{
			return Cleanup(dr, index, -1, true);
		}
		public static string Cleanup(string fld)
		{
			if (fld == "")
				return "";
			string outp;
			outp = fld.Replace("$", "").Trim(); //PLA2090602 - remove replace of quotes
			outp = outp.TrimEnd(','); // remove any extra commas from the end of the line.

			return outp.Trim();
		}
		public static string Cleanup(object fld)
		{
			string inp = "";
			try
			{
				inp = fld.ToString();
			}
			catch
			{
				return "";
			}

			return Cleanup(inp);
		}

		public DataTable FindData(HttpPostedFile selectedFile, string[] searchfields, string saveFolder)
		{
		    // if we have a save location, attempt to save the file
		    string fn = Path.GetFileName(selectedFile.FileName);

			if (saveFolder != "")
			{
				string sloc = saveFolder + "\\" + fn;
				try
				{
					selectedFile.SaveAs(sloc);
				}
				catch
				{

				}
			}
			StreamReader sr = new StreamReader(selectedFile.InputStream);
			DataTable dt = FindData(sr, searchfields);
			sr.Close();
			return dt; //FindData(, searchfields);
		}

		public DataTable FindData(StreamReader sr, string[] searchfields)
		{
			string ins = "";
			DataTable dt = new DataTable();
			object[] arr;
			int i;
			m_cols = searchfields.Length;
			int findfield = 0;
			arr = new Object[2];
			string[] stringra;

			for (i = 0; i < arr.Length; i++)
				dt.Columns.Add();

			while ((ins = sr.ReadLine()) != null)
			{
				if (ins == "\r" || ins.Trim() == "")
					continue;

				// if we are beyond the length of the fields, 
				// we have a complete dataset
				if (findfield >= searchfields.Length)
				{
					dt.Rows.Add(arr);
					findfield = 0;
					arr = new Object[2];
				}

				i = ins.IndexOf(searchfields[findfield]);
				if (i >= 0) // if found, look for next space and the vale then a space again
				{
                    stringra = ins.Substring(i + searchfields[findfield].Length + 1).Split(' ');
					arr[findfield] = stringra[0];
					findfield++;
				}
			}

			if (findfield == searchfields.Length - 1)
				dt.Rows.Add(arr);

			sr.Close();
			return dt;
		}

		#region import routines
        /* Import - allows the user to specify the CSV file being imported in multiple ways,
         *          and returns a datatable or rows that were successfully imported.
         * 
         * by: Phil Allison
         * dt: 6/4/09
         * Different calls:
         *  HttpPostedFile - file that was uploaded using the HtmlInputFile control
         *  HttpPostedFile, string - string allows for a folder path to save the file into.
         *  string - the file name and location of a file to load.
         *  StreamReader - Stream on a File to be imported. 
         */
		public DataTable Import(HttpPostedFile selectedFile)
		{
			return Import(selectedFile, "");
		}
		public DataTable Import(HttpPostedFile selectedFile, string saveFolder)
		{
			// if we have a save location, attempt to save the file
			string fn = Path.GetFileName(selectedFile.FileName);

			if (saveFolder != "")
			{
				string sloc = saveFolder + "\\" + fn;
				try
				{
					selectedFile.SaveAs(sloc);
				}
				catch
				{
					
				}
			}

			StreamReader sr = new StreamReader(selectedFile.InputStream);
			DataTable dt = Import(sr);
			sr.Close();
			return dt; // Import(new StreamReader(selectedFile.InputStream));
		}
		public DataTable Import(string FileName)
		{
			if (File.Exists(FileName) == false)
				return null;

			StreamReader sr = new StreamReader(FileName);
			DataTable dt = Import(sr);
			sr.Close();
			return dt;// Import(new StreamReader(FileName));
		}
        public DataTable Import(StreamReader sr)
		{
			string       ins = "";
			DataTable    dt  = new DataTable();
			object[] arr;
			int i;

			if (m_HasHeaderRow) // the first row is the header row.
			{
				ins = sr.ReadLine();
				if (ins != null && ins != "" && ins != "\r")
				{
					arr = BreakupData(ins);
					m_cols = arr.Length;
					for (i = 0; i < arr.Length; i++)
						dt.Columns.Add(arr[i].ToString());
				}
			}

            while ((ins = sr.ReadLine()) != null)
			{
                if (ins == "\r" || ins.Trim() == "")
					continue;

                char[] trims = { ' ', ',' };
				ins = ins.Trim(trims);
				m_rows++;

				arr = BreakupData(ins);

				if (m_cols == 0)
				{
					m_cols = arr.Length;
                    for (i = 0; i < arr.Length; i++)
						dt.Columns.Add();
				}
				if (m_cols < arr.Length)
				{
					for (; m_cols < arr.Length; m_cols++)
						dt.Columns.Add();

					dt.Rows.Add(arr);
				}
				else
					dt.Rows.Add(arr);
			}

			sr.Close();
			return dt;
        }

		public DataTable ImportFrom(string Data)
		{
			Data = Data.Trim();

			try
			{
				byte[] byteArray = Encoding.ASCII.GetBytes(Data);
				MemoryStream stream = new MemoryStream(byteArray);
				// convert stream to string

				StreamReader sr = new StreamReader(stream);
				DataTable dt = Import(sr);
				sr.Close();
				return dt;
			}
			catch
			{
				return new DataTable();
			}
		}
		#endregion

		#region export routines
		/* Export - allows the system to create a CSV file from a DataTable.
		 * 
		 * by: Phil Allison
		 * dt: 6/4/09
		 * Parameters:
		 *  DataTable - Table containing data to be exported.
		 *  int[]    - list of columns from the data to be exported. e.g. {0,1,3,4}
		 *  string[] - Headers for the columns.
		 *  string - Name of the file to be created.
		 */

		public void ExportToFile(DataTable DetailsTable, string FileName)
		{
			int[] cl = new int[DetailsTable.Columns.Count];
			string[] hd = new string[DetailsTable.Columns.Count];
			for (int x = 0; x < cl.Length; x++)
			{
				cl[x] = x;
				hd[x] = "";
			}
			ExportToFile(DetailsTable, cl, hd, FileName);
		}
		public void ExportToFile(DataTable DetailsTable, int[] ColumnList, string[] Headers, string FileName)
		{
			JustPlainExportToFile(DetailsTable, ColumnList, Headers, FileName);
		}
		public void ExportToFile(DataTable DetailsTable, string[] ColumnList, string[] Headers, string FileName)
		{
			JustPlainExportToFile(DetailsTable, ColumnList, Headers, FileName);
		}
		public void ExportToFile(DataTable DetailsTable, string[] ColumnList, string FileName)
		{
			HasHeaderRow = false;
			JustPlainExportToFile(DetailsTable, ColumnList, ColumnList, FileName);
		}

        public void Export(DataTable DetailsTable)
        {
            Export(DetailsTable, "temp.csv");
        }
        public void Export(DataTable DetailsTable, string FileName)
        {
            int[]    cl = new int[DetailsTable.Columns.Count];
            string[] hd = new string[DetailsTable.Columns.Count];
            for (int x = 0; x < cl.Length; x++)
            {
                cl[x] = x;
                hd[x] = "";
            }
            Export(DetailsTable, cl, hd, FileName);
        }
		public void Export(DataTable DetailsTable, string[] ColumnList, string[] Headers, string FileName)
		{
			JustPlainExport(DetailsTable, ColumnList, Headers, FileName, false);
		}
        public void Export(DataTable DetailsTable, int[] ColumnList, string[] Headers, string FileName)
        {
			JustPlainExport(DetailsTable, ColumnList, Headers, FileName, false);
		}

        public void Export(DataTable DetailsTable, string[] ColumnList, string[] Headers, string FileName, bool AddCRLF)
        {
            JustPlainExport(DetailsTable, ColumnList, Headers, FileName, AddCRLF);
        }
        //public void Export(DataTable DetailsTable, string[] ColumnList, string[] Headers, string FileName, bool AddCRLF)
        //{
        //    JustPlainExport(DetailsTable, ColumnList, Headers, FileName, AddCRLF);
        //}
		public void Export(DataTable DetailsTable, int[] ColumnList, string[] Headers, string FileName, bool AddCRLF)
		{
			JustPlainExport(DetailsTable, ColumnList, Headers, FileName, AddCRLF);
		}
		#endregion


		#region Private Functions

		private void JustPlainExport(DataTable dt, string[] ColumnList, string[] Headers, string FileName, bool AddCRLF)
        {
            HttpResponse Response = System.Web.HttpContext.Current.Response;

            try
            {
                StringWriter sw = new StringWriter();

				//PLA20101103 - replace worker code with generic function for both export to file and export
				sw = WriteData(sw, dt, ColumnList, Headers, AddCRLF);
                Response.Clear();
                Response.Buffer = true;

                Response.ContentType = "text/csv";
                Response.AppendHeader("content-disposition", "attachment; filename=" + FileName);
                Response.Write(sw.ToString());
				HttpContext.Current.ApplicationInstance.CompleteRequest();
                //response.End();
            }
            catch (Exception Ex)
            {
                Email.SendError("CSV Export", Ex);
            }
        }

        private void JustPlainExport(DataTable dt, int[] ColumnList, string[] Headers, string FileName, bool AddCRLF)
        {
			HttpResponse Response = System.Web.HttpContext.Current.Response;

            try
            {
                StringWriter sw = new StringWriter();

				//PLA20101103 - replace worker code with generic function for both export to file and export
				sw = WriteData(sw, dt, ColumnList, Headers, AddCRLF);
				Response.Clear();
				Response.Buffer = true;

				Response.ContentType = "text/csv";
				Response.AppendHeader("content-disposition", "attachment; filename=" + FileName);
				Response.Write(sw.ToString());
				HttpContext.Current.ApplicationInstance.CompleteRequest();
                //response.End();
            }
            catch (Exception Ex)
            {
                Email.SendError("CSV Export", Ex);
            }
        }

		private void JustPlainExportToFile(DataTable dt, int[] ColumnList, string[] Headers, string FileName)
		{
			if (FileName.IndexOf(":") < 0 && FileName.StartsWith("\\\\") == false)
			{
				FileName = BaseStaticData.PhysicalPath + FileName;
				FileName = FileName.Replace("file:\\", "");
			}
			try
			{
				StreamWriter sw = File.CreateText(FileName);

				//PLA20101103 - replace worker code with generic function for both export to file and export
				sw = WriteData(sw, dt, ColumnList, Headers, _crlf);
				sw.Close();
			}
			catch (Exception Ex)
			{
				Email.SendError("CSV Export", Ex);
			}
		}
		private void JustPlainExportToFile(DataTable dt, string[] ColumnList, string[] Headers, string FileName)
		{
			if (FileName.IndexOf(":") < 0 && FileName.StartsWith("\\\\") == false)
			{
				FileName = BaseStaticData.PhysicalPath + FileName;
				FileName = FileName.Replace("file:\\", "");
			}
			try
			{
				if (File.Exists(FileName))
					File.Delete(FileName);
				StreamWriter sw = File.CreateText(FileName);

				//PLA20101103 - replace worker code with generic function for both export to file and export
				sw = WriteData(sw, dt, ColumnList, Headers, _crlf);
				sw.Close();
			}
			catch (Exception Ex)
			{
				Email.SendError("CSV Export", Ex);
			}
		}

		private StreamWriter WriteData(StreamWriter sw, DataTable dt, string[] ColumnList, string[] Headers, bool AddCRLF)
		{
			StringWriter sw1 = new StringWriter();
			sw1 = WriteData(sw1, dt, ColumnList, Headers, AddCRLF);
			sw.Write(sw1);
			return sw;
		}
		private StreamWriter WriteData(StreamWriter sw, DataTable dt, int[] ColumnList, string[] Headers, bool AddCRLF)
		{
			StringWriter sw1 = new StringWriter();
			sw1 = WriteData(sw1, dt, ColumnList, Headers, AddCRLF);
			sw.Write(sw1);
			return sw;
		}

		private StringWriter WriteData(StringWriter sw, DataTable dt, string[] ColumnList, string[] Headers, bool AddCRLF)
		{
			//build the data string first.
			string val = "";
			int x = 0;

			try
			{
				if (HasHeaderRow)   // 20100624 1247 BPL - Implemented option to include header row
					sw.WriteLine(String.Join(m_sep, Headers));

				foreach (DataRow dr in dt.Rows)
				{
					try
					{
						for (x = 0; x < ColumnList.Length; x++)
						{
							val = dr[ColumnList[x]].ToString().Trim();
							if (val.IndexOf("\"") > -1 || (_alwaysQuote && !CASTHelper.isNumeric(val)))
							{
								val = "\"" + val.Replace("\"", "\"\"") + "\"";
							}
							if (val.IndexOf(",") > -1 && val.Substring(0, 1) != "\"") //if a comma is in the field, put quotes around it
								val = "\"" + val + "\"";
							sw.Write(val);
							if (x != ColumnList.Length - 1)
								sw.Write(m_sep);
							else
								sw.Write((AddCRLF) ? "\r\n" : "\r");

						}
					}
					catch (Exception ex)
					{
						Email.SendError("CSV Column: " + dr[ColumnList[0]].ToString(), ex);
					}
				}
			}
			catch (Exception ex)
			{
				Email.SendError("Data Error - " + Headers.ToString(), ex);
			}
			return sw;
		}
		private StringWriter WriteData(StringWriter sw, DataTable dt, int[] ColumnList, string[] Headers, bool AddCRLF)
		{
			//build the data string first.
			string val = "";
			int x = 0;

			if (HasHeaderRow)   // 20100624 1247 BPL - Implemented option to include header row
				sw.WriteLine(String.Join(m_sep, Headers));

			foreach (DataRow dr in dt.Rows)
			{
				for (x = 0; x < ColumnList.Length; x++)
				{
					val = dr[ColumnList[x]].ToString().Trim();
					if (val.IndexOf("\"") > -1 || (_alwaysQuote && !CASTHelper.isNumeric(val)))
					{
						val = "\"" + val.Replace("\"", "\"\"") + "\"";
					}
					if (val.IndexOf(",") > -1 && val.Substring(0, 1) != "\"") //if a comma is in the field, put quotes around it
						val = "\"" + val + "\"";
					sw.Write(val);
					if (x != ColumnList.Length - 1)
						sw.Write(m_sep);
					else
						sw.Write((AddCRLF) ? "\r\n" : "\r");
				}
			}
			return sw;
		}

		private object[] BreakupData(string ins)
		{
			/*
			 * while data should be formatted with commas between each field, need to take care of the follow potential issues:
			 * quotes:            "CARR7402",199.98,3106
			 * commas w/i quotes: CARR7406,"1,113.98",3111
			 * tabs:              CARR7450	165.98	3110
			 * spaces:			  CARR7410 113.98 3114
			 * quotes: w/i text: HYDR3004,8.58,338,"12""cycle bike" 
			 * best line of attack: Scrub the row first:
			 * - look for commas. if found, use comma as the seperator
			 * - replace tabs with commas. 
			 * - If still no commas, replace spaces with commas
			 * Row should now be scrubbed. Break up the field into comma delimited.
			 * Replace any two quotes ("") with a \"
			 * if there is a quote in a field, concat that field with the next field until another quote is found. Ignore \"
			 *
			 */

			// this line cleans out control characters (non-ASCII). 
			// going to try and control this before it gets here at the moment. 
			//ins = Regex.Replace(ins, @"[^\u0000-\u007F]", "");
			ins = ScrubRow(ins);

			string[] arr = ins.Split(',');
			return ScrubCols(arr);
		}

    	private object[] ScrubCols(string[] ra)
		{
			/*
			 * Replace any two quotes ("") with a \"
			 * if there is a quote in a field, concat that field with the next field until another quote is found. Ignore \"
			 */
			ArrayList fra = new ArrayList(); //string[1];

            int i = 0, q = 0;
			string tstr = "";
			bool openquote = false;
			for (int x = 0; x < ra.Length; x++)
			{
				tstr = ra[x].Replace("\"\"", "~~"); //temp replace double quotes with a double tilde

				q = tstr.IndexOf("\"");
                if (q >= 0)
				{
                    q = tstr.IndexOf("\"", q + 1);
					//make sure and check for a close quote within the existing string as well.
					if (q <= 0)
					{
						if (openquote)//if we already have an open quote, then add this to the current field and close it
						{
							openquote = false;
						}
						else
						{
							openquote = true;
						}
					}
				}

				if (i >= fra.Count)
					fra.Add("");

				fra[i] += tstr.Replace("\"", "").Replace("~~", "\"").Trim();
				if (openquote == false && (fra[i].ToString() != "" || tstr == "")) // need to allow for empty string
					i++;
			}

			return fra.ToArray();
		}

		private string ScrubRow(string ins)
		{
			/* best line of attack: Scrub the row first:
			 * - look for commas. if found, use comma as the seperator
			 * - replace tabs with commas. 
			 * - If still no commas, replace spaces with commas
			*/
			int x = ins.IndexOf(i_sep);

			if (x > 0)
				return ins;

			ins = ins.Replace("\t", i_sep);
			if (x <= 0)
				x = ins.IndexOf(i_sep);

			//PLA2090602 - NOTE Need to NOT replace spaces within quote sets.
            if (x <= 0)
				ins = ins.Replace(" ", i_sep);

			return ins;
        }
        #endregion

    }
}
